package dbh_helper;

require Exporter;


our @ISA = qw(Exporter);
our $VERSION = 1.00;
our @EXPORT = qw(dbh_get_single_value dbh_get_nth_value dbh_get_median dbh_get_tables_regex dbh_get_table_columns);



use strict;
use warnings;
use DBI;
use DBD::Pg;
use Carp;


#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888
#
#
#	DBI queries
#
#		Wrapper to obtain a value or the median from a database table
#
#


#_________________________________________________________________________________________

#	get_exons_from_both_species

#_________________________________________________________________________________________
sub get_exons_from_both_species($$$\%\%\@)
{
	my ($dbh, $species1,  $species2, $exon_lists, $gene_to_species, $genes_per_species) = @_;
	#
	#	get exon loci which may overlap from both species
	#
	print STDERR "\tGet exon loci which may overlap from both species...\n";
	my $sql_cmd =<<"PL/SQLCMD";
	SELECT  
			gene_id, 
			gl.species = '$species2',
			chromosome, 
			strand, 
			pe.exon_beg, 
			pe.exon_end 
		FROM 
			gene_chromosome_strand gl JOIN 
			id_map USING (gene_id) JOIN 
			cdna_exons pe USING (prot_id) 
		WHERE 
			gl.species = '$species1' OR 
			gl.species = '$species2' 
PL/SQLCMD
	my $data = $dbh->selectall_arrayref($sql_cmd)		or croak DBI::errstr. ".";
	for my $row (@$data)
	{
		my ($gene_id, $species_id, $chrm, $strand,  $beg,  $end) = @$row;
		push(@{$exon_lists->{$chrm.$strand}},  [$beg, $end, $gene_id]);
		$gene_to_species->{$gene_id} = $species_id;
		++$genes_per_species->[$species_id]{$gene_id};
	}
}

#_________________________________________________________________________________________

#	dbh_get_tables_re

#_________________________________________________________________________________________
sub dbh_get_tables_regex($$)
{
	my ($dbh, $table_name) = @_;
	my $sql_cmd =<<"PL/SQLCMD";
	SELECT  
			table_name
		FROM 
			information_schema.tables 
		WHERE 
			table_name ~ '$table_name';
PL/SQLCMD
	my $data = $dbh->selectall_arrayref($sql_cmd)		or croak DBI::errstr. ".";
	return map {$_->[0]} @$data;
}

#_________________________________________________________________________________________

#	dbh_get_table_columns

#_________________________________________________________________________________________
sub dbh_get_table_columns($$)
{
	my ($dbh, $table_name) = @_;
	my $sql_cmd =<<"PL/SQLCMD";
	SELECT  
			column_name 
		FROM 
			information_schema.columns
		WHERE 
			(table_schema || '_' || table_name) ~ '$table_name';
PL/SQLCMD
	my $data = $dbh->selectall_arrayref($sql_cmd)		or croak DBI::errstr. ".";
	return map {$_->[0]} @$data;
}

#_________________________________________________________________________________________

#	dbh_get_median

#_________________________________________________________________________________________
sub dbh_get_single_value($$)
{
	my ($dbh, $sql_txt) = @_;
	my @results;
	eval
	{
		@results = $dbh->selectrow_array($sql_txt);
	};
	if ($@ || defined $dbh->err)
	{
		carp "Error:\n\t".$dbh->errstr."\n\t[$sql_txt]\n";
		return undef;
	}
	return $results[0];
}

sub dbh_get_nth_value($$$$)
{
	my ($dbh, $n_pos, $table, $var) = @_;
	return dbh_get_single_value($dbh, "SELECT $var FROM $table ORDER BY $var ".
										"OFFSET $n_pos LIMIT 1");
}
sub dbh_get_median($$$)
{
	my ($dbh, $table, $var) = @_;
	my $sql = "SELECT count(*) FROM $table ";
    if ($table =~ /where/i)
	{
		$table .= " AND NOT $var is null";
	}
	else
	{
		$table .= " WHERE NOT $var is null";
	}
	my $total_cnt = dbh_get_single_value($dbh, "SELECT count(*) FROM $table");
	my $median_pos	= int($total_cnt / 2);
	my $lower_pos	= int($total_cnt * 0.166666666667);
	my $upper_pos	= int($total_cnt * 0.833333333333);
	my $median		= dbh_get_nth_value($dbh, $median_pos, $table, $var);
	my $lower		= dbh_get_nth_value($dbh, $lower_pos, $table, $var);
	my $upper		= dbh_get_nth_value($dbh, $upper_pos, $table, $var);
	return ($lower, $median, $upper, $total_cnt);
}
#
#
#	DBI queries
#
#		Wrapper to obtain a value or the median from a database table
#
#
#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888



return 1;
